/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.report;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.comm.DBConnect;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;
import com.jarp.db.DAO;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 *
 * @author 赵远
 */
public class Fact_table {

    public String Create_facttable(String repid) {
        Date date=new Date();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
        String time=sdf.format(date);
        String sret = "";
        try {
            
            FillDataDAO filldao = new FillDataDAO();
            RepDataVO repVo = filldao.getRepDemension(repid);
            if (repVo == null) {
                return sret;
            }
            String stype = repVo.getSourcetype();
//            if (stype != null && stype.equals(ConstUtil.AUTOTYPE)) {//自动抽取1
//                return "auto";
//            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (repDemLst == null || repDemLst.size() == 0) {
                return sret;
            }
            DAO dao = new DAO();
            String tablename = ConstUtil.FACTPRE + repVo.getRepcode();
            String Drop_sql = "RENAME  " + tablename+" TO "+tablename+"_"+time;
            String rep_sql = "update tb_report set reffact='" + tablename + "',REPVALUECOL='REPVAL' where repid=" + repid;
            StringBuffer sql = new StringBuffer();
            sql.append("create table " + tablename + "(");
            StringBuffer buff = new StringBuffer();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                buff.append(facdimcol);
                if (j != repDemLst.size() - 1) {
                    buff.append(",");
                }
                if (ConstUtil.DBTYPE.equals("DB2")) {
                    sql.append(facdimcol + " BIGINT NOT NULL,");
                } else if (ConstUtil.DBTYPE.equals("ORACLE")) {
                    sql.append(facdimcol + " VARCHAR2(32) NOT NULL,");
                }else if(ConstUtil.DBTYPE.equals("MYSQL")){
                	sql.append(facdimcol + " VARCHAR(32) NOT NULL,");
                }
            }
            sql.append("REPVAL decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("SUMVAL decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("TREPVAL decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("TSUMVAL decimal(14," + repVo.getDataprec() + ") ,");
             sql.append("YOYRATION decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("MONRATION decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("TYOYRATION decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("TMONRATION decimal(14," + repVo.getDataprec() + ") ,");
             sql.append("PREVAL decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("TPREVAL decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("COLVAL1 decimal(14," + repVo.getDataprec() + ") ,");
            sql.append("COLVAL2 decimal(14," + repVo.getDataprec() + ") ,");
             sql.append("COLVAL3 decimal(14," + repVo.getDataprec() + ") ,");
             sql.append(" version bigint default 1 ,");
             if (ConstUtil.DBTYPE.equals("DB2")) {
            	 sql.append("COLVAL4 VARCHAR(64)  ,");
                 sql.append("COLVAL5 VARCHAR(64)  ,");
             } else if (ConstUtil.DBTYPE.equals("ORACLE")) {
            	 sql.append("COLVAL4 VARCHAR2(64)  ,");
                 sql.append("COLVAL5 VARCHAR2(64)  ,");
             }
            sql.append("USERID VARCHAR(32) , "); //操作人帐号
            sql.append("USERNAME VARCHAR(64) , "); //操作人名
            if (ConstUtil.DBTYPE.equals("DB2")) {
            	sql.append("UPDATETIME TIMESTAMP DEFAULT CURRENT TIMESTAMP, "); //操作日期
            } else if (ConstUtil.DBTYPE.equals("ORACLE")) {
            	sql.append("UPDATETIME TIMESTAMP, "); //操作日期
            }
           // sql.append("COMMENT VARCHAR(128) , ");
            sql.append("constraint pk_" + tablename + " primary key (");
            sql.append(buff);
            sql.append("))");
            System.out.println("create table:"+sql);

            boolean flag=false;       //这个作为标记事实表创建是否成功
            try {
                Connection con = DBConnect.getODSConnection();
                Statement state = con.createStatement();
                Statement state2 = con.createStatement();
                try {
                    state.execute(Drop_sql);
                    state.close();
                } catch (Exception e) {
                    state.close();
                }
                try {
                   state2.execute(sql.toString());
                    flag=true;
                    state2.close();
                } catch (Exception e) {
                    sret = "wrong";
                    state2.close();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Fact_table.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                try {
                    DBConnect.retConnectionODS();
                } catch (SQLException ex) {
                    Logger.getLogger(Fact_table.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if(flag){
            int iR = dao.execCommand(ConstUtil.DBNAME, rep_sql);
                    if (iR > 0) {
                        sret = "ok";
                    }
            }
            
        } catch (ServletException ex) {
            Logger.getLogger(Fact_table.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sret;
    }


    public String setRepdata(String repid) {
        String ret = "";
//        RepDataVO repVO = getRepdata(jsonObj);
        RepDataVO repVO = new FillDataDAO().getRepDemension(repid);
        DAO dao = new DAO();
        if (repVO == null) {
            return ret;
        }
        ArrayList<String> sqlLst = new ArrayList<String>();
        StringBuffer strBuff = new StringBuffer();
        StringBuffer tablesBuff = new StringBuffer();
        StringBuffer fieldsBuff = new StringBuffer();
        StringBuffer facdimcolBuff = new StringBuffer();
        StringBuffer maxlevBuffer = new StringBuffer();
        try {
            String facTable = repVO.getReffact();

            sqlLst.add("delete from " + facTable);
            strBuff.append("insert into " + facTable + "  ");
            ArrayList<RepDemensionVO> repDemLst = repVO.getRepDemLst();
            if (repDemLst == null || repDemLst.size() == 0) {
                return "no demension";
            }
            int iDemSize = repDemLst.size();
            StringBuffer sjBuff = new StringBuffer();
            if (iDemSize > 0) {
                for (int i = 0; i < iDemSize; i++) {
                    RepDemensionVO repDemVO = (RepDemensionVO) repDemLst.get(i);
                    String demtype = repDemVO.getDemtype();
                    String tablename = repDemVO.getDimtable();
                    String dimidcol = repDemVO.getIdcol();
                    String facdimidcol = repDemVO.getFactdimcol();
                    String levelcol = repDemVO.getLevelcol();
                    facdimcolBuff.append(facdimidcol);
                    tablesBuff.append(tablename);
                    fieldsBuff.append(tablename).append(".").append(dimidcol);
                    if (i != iDemSize - 1) {
                        tablesBuff.append(",");
                        fieldsBuff.append(",");
                        facdimcolBuff.append(",");
                    }
//                    if (demtype != null && demtype.equals("2")) {
//                        sjBuff.append("\n where " + tablename + "." + dimidcol + " >='2010' and " + tablename + "." + dimidcol + " <='2013'");
//                    }
                    maxlevBuffer.append("\n and "+tablename+"."+levelcol+" = (select max("+tablename+"."+levelcol+") from "+tablename+")");
                }
            }
            strBuff.append(" ( "+facdimcolBuff+","+repVO.getRepvaluecol()+",SUMVAL ) ");
            strBuff.append(" select ");
            strBuff.append(fieldsBuff.toString());
            strBuff.append("\n ,0,0  from ");
            strBuff.append(tablesBuff.toString());
            strBuff.append(sjBuff.toString());
            sqlLst.add(strBuff.toString());
            sqlLst.add("update " + facTable + " set " + repVO.getRepvaluecol() + " = NULL ,SUMVAL = NULL");
            System.out.println(sqlLst);
            if (sqlLst.size() > 0) {
                if (dao.execCommand(ConstUtil.DBNAMEODS, sqlLst)) {
                    ret = "ok";
                } else {
                    ret = "failure";
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("RepData setRepdata Exception.");
        }
        return ret;
    }
    
    public String  createtable(){
        String res="";
        DAO dao=new DAO();
        String sql="SELECT distinct a.REPID from TB_REP_DEMENSION a "
                + "LEFT JOIN TB_REPORT b on a.REPID=b.REPID "
                + "WHERE b.REFFACT is NULL";
        try {
            Vector v=dao.getDataSet(ConstUtil.DBNAME, sql);
            String [] repid=new String[v.size()];
            System.out.println("一共有"+v.size()+"条数据");
            if(v!=null){
                for(int i=0;i<v.size();i++){
                    Hashtable hash=(Hashtable)v.get(i);
                    repid[i]=(String)hash.get("REPID");
                }
                boolean b=true;
                int j=0;
                for(int i=0;i<repid.length;i++){
                    String ret=this.Create_facttable(repid[i]);
                    if(ret.equals("wrong")){
                        b=false;
                        System.out.println("创建失败");
                    }
                    else{
                        j++;
                        System.out.println("已创建"+j+"个事实表");
                    }
//                    this.setRepdata(repid[i]);
                }
                if(b==true){
                    res="ok";
                }
                else{
                    res="wrong";
                }
            }
        } catch (ServletException ex) {
            Logger.getLogger(Fact_table.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }
}
